package com.dst.dao.impl;

import com.dst.dao.BrandDao;
import com.dst.eneity.Brand;
import com.dst.eneity.Phone;
import com.dst.util.JdbcUtils;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class BrandDaoimpl implements BrandDao {
    @Override
    public List<Brand> selectAllBrandDao() {
        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        List<Brand> list=new ArrayList<>();
        try{
            conn = JdbcUtils.getConnection();
            String sql="select * from t_brand";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                Brand brand=new Brand(id,name);
                list.add(brand);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public void addBrandDao(Brand brand) {
        Connection conn=null;
        PreparedStatement pstmt=null;
        try{
            conn = JdbcUtils.getConnection();
            String sql="insert into t_brand(name) values (?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,brand.getName());
            pstmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public Brand selectPinpai(String pinpai) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Brand brand=null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select * from t_brand where name=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,pinpai);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                brand=new Brand(id,name);
            }
        }catch (Exception e){
            e.printStackTrace();
        }  return brand;
    }

    @Override
    public void deleteBrandDao(int id) {
        Connection conn=null;
        PreparedStatement pstmt=null;
        try{
            conn = JdbcUtils.getConnection();
            String sql="delete  from t_brand  where  id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            int i = pstmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public Brand selectPinpai(int id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Brand brand=null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select * from t_brand where name=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int id1 = rs.getInt("id");
                String name = rs.getString("name");
                brand=new Brand(id1,name);
            }
        }catch (Exception e){
            e.printStackTrace();
        }  return brand;
    }

    @Override
    public List<Brand> selectAllBrand() {
        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        List<Brand> list=new ArrayList<>();
        try{
            conn = JdbcUtils.getConnection();
            String sql="SELECT b.id,b.name,count(*) bname\n" +
                    "from t_phone p left JOIN t_brand b on p.brand_id=b.id\n" +
                    "GROUP BY b.id,b.name ";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int bname = rs.getInt("bname");
                Brand brand=new Brand(id,name,bname);
                list.add(brand);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }
}
